package com.stm.bi.jdbc;

import com.stm.framework.core.util.ClassUtil;
import com.stm.framework.core.util.DateUtils;
import com.stm.framework.core.util.HumpUtil;
import com.stm.framework.core.util.SpringContextUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author chenyingjun
 * @Desc
 * @Date 2024/12/2 16:55
 */
public class DorisJdbcUtils {
    public static final Logger logger = LoggerFactory.getLogger(DorisJdbcUtils.class);
    public final static String SELECT_COLUMN_SQL = "select column_name,data_type from information_schema.columns where table_schema=? and table_name=?";
    public final static List<String> DATA_TYPE_STRING_ARRAY = Arrays.asList(new String[]{"varchar","longtext","text","char","tinytext","mediumtext"});
    public final static List<String> DATA_TYPE_NUMBER_ARRAY = Arrays.asList(new String[]{"int","tinyint","smallint","double","bigint","long","float","decimal"});
    public final static List<String> DATA_TYPE_DATE_TIME_ARRAY = Arrays.asList(new String[]{"timestamp","datetime"});
    public final static List<String> DATA_TYPE_DATE_ARRAY = Arrays.asList(new String[]{"date"});

    public static final int BATCH_INSERT_SIZE = 2000;

    public static void batchCreate(DataSourceEnum dataSourceEnum, String tableName, List<Map> dataList){
        JdbcTemplate jdbcTemplate = new JdbcTemplate(SpringContextUtil.getBean(dataSourceEnum.getDataSource()));
        batchCreate(jdbcTemplate,tableName,dataList);
    }

    public static void batchCreate(String tableName, List<Map> dataList){
        JdbcTemplate jdbcTemplate = SpringContextUtil.getBean(JdbcTemplate.class);
        batchCreate(jdbcTemplate,tableName,dataList);
    }

    /**
     * 加载数据到指定的数据源
     *
     * @param tableName 表名
     * @param dataList 待插入的数据列表，每个数据项为一个映射
     */
    public static void batchCreate(JdbcTemplate jdbcTemplate,String tableName, List<Map> dataList){
        DataSource dataSource = jdbcTemplate.getDataSource();
        String schema = (String)ClassUtil.getFieldValueByName( dataSource,"schema");
        // 创建JdbcTemplate实例，用于执行SQL语句
        List<Map<String, Object>> columnList = getColumnList(jdbcTemplate, schema, tableName);

        // 分批处理数据列表
        for (int i = 0; i < dataList.size(); i += BATCH_INSERT_SIZE) {
            // 获取当前批次的数据列表
            List<Map> list = dataList.subList(i, Math.min(i + BATCH_INSERT_SIZE, dataList.size()));
            // 构建插入SQL语句的列名部分
            String insertSql = "insert into " + schema + "." + tableName +
                    "(" + columnList.stream().map(map -> map.get("column_name").toString()).reduce((s, s2) -> s + "," + s2).get() + ") " +
                    "values";
            // 构建插入SQL语句的值部分
            insertSql += list.stream().map(rowMap -> {
               String values = (String) columnList.stream().map(columnMap ->{
                   String dataType = (String) columnMap.get("data_type");
                   String columnName = (String) columnMap.get("column_name");
                   Object rowValue = rowMap.get(columnName);
                   if(rowValue == null){
                       String humpColumnName = HumpUtil.underline2hump(columnName);
                       rowValue = rowMap.get(humpColumnName);
                       if(rowValue == null && columnName.startsWith("is_")){
                           rowValue = rowMap.get(HumpUtil.underline2hump(columnName.substring(3)));
                       }
                   }
                   return convert(dataType,rowValue);
               }).reduce((s, s2) -> s + "," + s2).get();
               return "(" +values + ")";
            }).reduce((s, s2) -> s + "," + s2).get();
            try {
                long startTime = System.currentTimeMillis();
                // 执行插入操作
                int res = jdbcTemplate.update(insertSql, new Object[]{});
                long endTime = System.currentTimeMillis();
                logger.info("执行插入操作,成功{}条数，耗时：{}ms",res,(endTime-startTime));

            }catch (Exception e){
                // 异常处理：打印异常信息
                logger.error("执行插入操作异常，e="+e.getMessage(),e);
                throw e;
            }
        }
    }

    public static List<Map<String, Object>> getColumnList(JdbcTemplate jdbcTemplate, String schemaName, String tableName) {
        // 查询表的列信息
        List<Map<String,Object>> columnList = jdbcTemplate.queryForList(SELECT_COLUMN_SQL,new Object[]{schemaName, tableName});
        return columnList;
    }

    /**
     * 根据数据库数据类型转换值
     *
     * @param dbDataType 数据库中的数据类型
     * @param value 待插入的值
     * @return 转换后的值，适合数据库插入
     */
    public static Object convert(String dbDataType,Object value){
        // 如果值为null，则直接返回null
        if(value == null){
            return null;
        }
        // 根据不同的数据类型，进行相应的转换处理
        if(DATA_TYPE_STRING_ARRAY.contains(dbDataType)){
            return ("'"+value+"'").replace("|", "/");
        }else if(DATA_TYPE_NUMBER_ARRAY.contains(dbDataType)){
            return value;
        }else if(DATA_TYPE_DATE_TIME_ARRAY.contains(dbDataType)){
            if(value instanceof LocalDateTime){
                return "'"+((LocalDateTime)value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))+"'";
            }else if(value instanceof LocalDate){
                return "'"+((LocalDate)value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))+" 00:00:00'";
            }else if(value instanceof Date){
                return "'"+(DateUtils.parseDateToStr((Date)value, "yyyy-MM-dd HH:mm:ss"))+"'";
            }else if(value instanceof Long){
                return "'"+(DateUtils.parseDateToStr(new Date((Long)value), "yyyy-MM-dd HH:mm:ss"))+"'";
            }else {
                return "'"+value+"'";
            }
        }else if(DATA_TYPE_DATE_ARRAY.contains(dbDataType)){
            if(value instanceof LocalDateTime){
                return "'"+((LocalDateTime)value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))+"'";
            }else if(value instanceof LocalDate){
                return "'"+((LocalDate)value).format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))+"'";
            }else if(value instanceof Date){
                return "'"+(DateUtils.parseDateToStr((Date)value, "yyyy-MM-dd"))+"'";
            }else if(value instanceof Long){
                return "'"+(DateUtils.parseDateToStr(new Date((Long)value), "yyyy-MM-dd"))+"'";
            }else{
                return "'"+value+"'";
            }
        }else{
            return ("'"+value+"'").replace("|", "/");
        }
    }
}
